# -*- coding: utf-8 -*-
import pandas as pd
from MySQLdb import connect, cursors
import configparser
import os
import seaborn as sns
import matplotlib.pyplot as plt
import socket
import math
import numpy as np
import random as rd

#last_update_date = '2020-08-27 00:00:00'

def construct_db_connections():
    """进行数据库的连接设置，使用的是sql库,输出是库表连接，用于连接数据库
    Returns
    -------
    jy_conn: TYPE
        DESCRIPTION.
    zj_conn: TYPE
        DESCRIPTION.

    """
    hostname = socket.gethostname()
    hostname_prefix = hostname.split('-')[0]
    is_server = hostname_prefix in ('dev00', 'online00', 'online01')

    if not is_server:
        """
        In local environment, you should put a configuration file named
        '.db_config.ini' in the current directory.
        The configuration file content format must be:
            [DEFAULT]
            user=<YOUR_DB_USERNAME>
            password=<YOUR_PASSWORD>
            schema=<YOUR_OWN_SCHEMA>

        NOTE: Please do NOT commit the configuration file to svn
        """
        pwd = os.getcwd()
        db_config_file_path = os.path.join(pwd, '.db_config.ini')
        assert os.path.exists(db_config_file_path), "db config file %s is not existed" % db_config_file_path
        parser = configparser.ConfigParser()
        parser.read(db_config_file_path)
        user = parser['DEFAULT']['user']
        password = parser['DEFAULT']['password']
        zj_schema = parser['DEFAULT']['schema']
        db_host = 'dev.zhijuninvest.com'
    else:
        user = 'cronjob'
        password = 'ZyYADQjvh68UjySZ'
        db_host = '172.17.0.1'
        zj_schema = 'zj_data'

    jy_conn = connect(host=db_host, port=3306, user=user, passwd=password,
                      db="JYDB", charset="utf8mb4", cursorclass=cursors.DictCursor)
    zj_conn = connect(host=db_host, port=3306, user=user, passwd=password,
                      db=zj_schema, charset="utf8mb4", cursorclass=cursors.DictCursor)

    return jy_conn, zj_conn

def get_industry_returns():
    query = """
        SELECT code, date, growth_rate
        FROM zj_data.index_daily_quote
        WHERE code like '80%'
        """
    industry_returns = pd.read_sql(query, zj_conn)
    return industry_returns

def get_industry_returns_by_code(code, start_date):
    query = f"""
        SELECT date, growth_rate
        FROM zj_data.index_daily_quote
        WHERE code = '{code}'
        AND date >= '{start_date}'
        """
    industry_returns = pd.read_sql(query, zj_conn)
    return industry_returns

def volatility(ret_list):
    # 传入需是simple return
    return np.std(ret_list) * math.pow(252, 0.5)

def annual_ret(ret_list):
    # 传入需是log return
    r = ret_list.sum()/(ret_list.notnull().sum()/252.0)
    return np.exp(r)-1


if __name__ == '__main__':
    jy_conn, zj_conn = construct_db_connections()
    plt.rcParams['font.sans-serif'] = ['PingFang HK']
    plt.rcParams['axes.unicode_minus'] = False

    #industry_returns = get_industry_returns()
    #print(industry_returns)
    industry_returns = pd.DataFrame([])
    '''
    industry_code_list = ["801010","801020","801030","801040","801050","801080","801110","801120","801130","801140","801150",
    "801160","801170","801180","801200","801210","801230","801710","801720","801730",
    "801740","801750","801760","801770","801780","801790","801880","801890"]
    industry_name_list = industry_code_list
    '''
    '''
    industry_code_list = ["HSI","000688", "000932","000928","000933","000935","000931","000937","000930","000936","000934","000929"]
    industry_name_list = ["港股","科创板", "中证消费","中证能源","中证医药","中证信息","中证可选","中证公用","中证工业","中证电信","中证金融","中证材料"]
    '''
    industry_code_list = ['000300', '000016', '399905', 'HSI', 'H11001','H11025', 'Au99.99']
    industry_name_list = ['沪深300', '上证50', '中证500', '港股', '中证全债', '中证货基', 'Au']
    start_date = "2005-01-01"

    for code in industry_code_list:
        print(code)
        industry_return_by_code = get_industry_returns_by_code(code, start_date)
        industry_return_by_code.set_index("date",inplace=True)
        industry_return_by_code["log_return"] = industry_return_by_code["growth_rate"].apply(lambda x: np.log(x + 1))
        industry_returns = pd.concat([industry_returns, industry_return_by_code["growth_rate"]], axis=1)
    industry_returns.columns = industry_name_list

    # 年化收益
    for code in industry_name_list:
        print(code, annual_ret(industry_returns[code]))

    print('risk')
    # 年化波动率
    for code in industry_name_list:
        print(code, volatility(industry_returns[code]))

    industry_returns.columns = industry_code_list
    cov_df = industry_returns.cov()
    cov_df.index = industry_name_list
    cov_df.columns = industry_name_list

    #cov_df.to_csv("covariance.csv")

    corr_df = industry_returns.corr()
    corr_df.index = industry_name_list
    corr_df.columns = industry_name_list

    corr_dict = {}
    for front_code in industry_name_list:
        slice = corr_df.loc[front_code]
        for rear_code in industry_name_list:
            corr_dict[front_code+"-"+rear_code] = slice[rear_code]
    #print(corr_dict)
    corr_df1 = pd.Series(corr_dict)

    #print(corr_df1.nlargest(48))
    #print(len(industry_code_list))
    print(corr_df)
    #corr_df.to_csv("correlation.csv")
    sns.heatmap(corr_df)
    plt.show()


